package com.springbootblog.dao.sqlBuilder;

import com.springbootblog.pojo.Browse;
import com.springbootblog.pojo.Comment;
import org.jetbrains.annotations.NotNull;

import java.util.Map;

public class ArticleSqlBuilder
{
    public String addComment(@NotNull Comment comment)
    {
        String sql = "INSERT INTO comment (uid,replyid,content,addtime,returnid,article_id) " +
                "VALUES " +
                "('"+comment.getUid()+"','"+comment.getReplyid()+"','"+comment.getContent()+"',NOW(),'"+comment.getReturnid()+"','"+comment.getArticle_id()+"' ) ";
        return sql;
    }

    public String getCommentInfo(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT * FROM comment WHERE id = '"+param.get("replyid")+"'  ";
        return sql;
    }

    public String getPlaceOnFileListByYear(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT id,DATE_FORMAT(putime,'%Y-%m-%d') as month,arttitle FROM article WHERE DATE_FORMAT(putime,'%Y') = '"+param.get("year")+"' AND is_show = 1 ORDER BY id DESC ";
        return sql;
    }

    public String getPlaceOnFileList(Map<String, Object> param)
    {
        String sql = "SELECT DATE_FORMAT(putime,'%Y') as year FROM article WHERE is_show = 1 GROUP BY DATE_FORMAT(putime,'%Y') ORDER BY id DESC ";
        return sql;
    }

    public String getArtCategoryInfoById(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT * from art_category where id = '"+ param.get("cate_id") +"'";
        return sql;
    }

    public String getCommentNumber(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT COUNT(1) AS number FROM comment WHERE article_id = "+param.get("article_id");
        return sql;
    }

    public String selectSearchPage(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT p1.*,DATE_FORMAT(p1.putime,'%Y-%m-%d') AS addtime,p3.cat_name FROM article AS p1 " +
                " LEFT JOIN article_info p2 ON p1.id = p2.article_id " +
                " LEFT JOIN art_category p3 ON p1.art_cid = p3.id " +
                " where " + param.get("where") +
                " ORDER BY p1.is_top DESC,p1.sort_no DESC, p1.id DESC  ";
        return sql;
    }

    public String selectSearchPageNum(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT COUNT(1) AS number FROM article WHERE "+param.get("where");
        return  sql;
    }

    public String getLabelInfoById(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT * from label where id = '"+ param.get("labelIdDecrypt") +"'";
        return sql;
    }

    public String getArtCategoryById(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT cat_pid from art_category where id = '"+ param.get("cate_id_son") +"'";
        return sql;
    }

    public String getArtCategoryByCatePid(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT * from art_category where cat_pid = '"+ param.get("cate_id") +"'";
        return sql;
    }

    public String getCommontByReplyId(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT p1.*,p2.nickname,p2.figureurl_qq,p2.figureurl_wx from comment p1 " +
                "LEFT JOIN user p2 on p1.uid = p2.id " +
                "where p1.id = '" + param.get("replyid") + "' AND status = 0 " +
                "limit 1";
        return sql;
    }

    public String getCommontTwoListByArticleId(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT p1.*,p2.nickname,p2.figureurl_qq,p2.figureurl_wx from comment p1 " +
                "LEFT JOIN user p2 on p1.uid = p2.id " +
                "where returnid = '"+ param.get("comment_id") +"' AND status = 0 AND article_id = '"+ param.get("article_id") +"' " +
                "order by addtime asc";
        return sql;
    }

    public String getCommontListByArticleId(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT p1.*,p2.nickname,p2.figureurl_qq,p2.figureurl_wx from comment p1 " +
                "LEFT JOIN user p2 on p1.uid = p2.id  " +
                "where p1.replyid = 0 AND p1.status = 0 AND p1.article_id = '"+ param.get("article_id") +"' " +
                "order by p1.addtime asc";
        return sql;
    }

    public String addBrowse(@NotNull Browse browse)
    {
        String sql = "INSERT INTO browse (username,article_title,brow_time,ip) " +
                "VALUES " +
                "('"+browse.getUsername()+"','"+browse.getArticleTitle()+"',NOW(),'"+browse.getIp()+"')" ;
        return sql;
    }

    public String getResourceById(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT * FROM resource WHERE id = " + param.get("resource_id");
        return sql;
    }

    public String getAfterArticle(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT * FROM article WHERE id > " + param.get("article_id") + " ORDER BY id ASC LIMIT 1 ";
        return sql;
    }

    public String getFrontArticle(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT * FROM article WHERE id < " + param.get("article_id") + " ORDER BY id DESC LIMIT 1 ";
        return sql;
    }

    public String getLabelByIdStr(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT * FROM label WHERE id IN (" + param.get("labelstr") + ")";
        return sql;
    }

    public String getArticleDetailById(@NotNull Map<String, Object> param)
    {
        String sql = "SELECT p1.resourse_id,p1.email,p1.is_show,p1.id,p1.artlogo,p1.another,p1.artdesc,p1.is_top,p1.sort_no,p1.status,p1.uptime,p1.arttitle,p1.art_cid,p1.labelstr,p1.click_num,DATE_FORMAT(p1.putime,'%Y-%m-%d') AS putime,p2.content FROM article p1 " +
                "LEFT JOIN article_info p2 ON p1.id = p2.article_id " +
                "WHERE p1.id = " + param.get("article_id") + " AND is_show = 1";
        return sql;
    }

    public String selectBySearchAndPage(@NotNull Map<String, Object> param) {
        String where = "";
        if(param.get("search") != null && !param.get("search").equals("") && !param.get("search").toString().isEmpty())
        {
            where = " WHERE p1.arttitle LIKE '%" + param.get("search") + "%'";
        }
        String Sql = "SELECT p1.resourse_id,p1.email,p1.is_show,p1.id,p1.artlogo,p1.another,p1.artdesc,p1.is_top,p1.sort_no,p1.status,p1.uptime,p1.arttitle,p1.art_cid,p1.labelstr,p1.click_num,DATE_FORMAT(p1.putime,'%Y-%m-%d') AS addtime,DATE_FORMAT(p1.putime,'%Y-%m-%d') AS putime,p3.cat_name FROM article AS p1 " +
                "LEFT JOIN article_info p2 ON p1.id = p2.article_id " +
                "LEFT JOIN art_category p3 ON p1.art_cid = p3.id " +
                where +
                "ORDER BY p1.is_top DESC,p1.sort_no DESC,p1.id DESC " +
                "LIMIT " + param.get("startNum") +","+ param.get("limit")+";";
        // System.out.println(Sql);
        return Sql;
    }

    public String getArticleList(Map<String, Object> param) {
        String Sql = "SELECT p1.resourse_id,p1.email,p1.is_show,p1.id,p1.artlogo,p1.another,p1.artdesc,p1.is_top,p1.sort_no,p1.status,p1.uptime,p1.arttitle,p1.art_cid,p1.labelstr,p1.click_num,DATE_FORMAT(p1.putime,'%Y-%m-%d') AS addtime,DATE_FORMAT(p1.putime,'%Y-%m-%d') AS putime,p3.cat_name,p2.content FROM article AS p1 " +
                "LEFT JOIN article_info p2 ON p1.id = p2.article_id " +
                "LEFT JOIN art_category p3 ON p1.art_cid = p3.id " +
                "ORDER BY p1.is_top DESC,p1.sort_no DESC,p1.id DESC ;";
        // System.out.println(Sql);
        return Sql;
    }

    /*public String selectBySearchAndPage(@Param("search") final String search, @Param("startNum") final int startNum, final @Param("limit") int limit) {
        String where = " ";
        if(search != null && !search.equals(""))
        {
            where = " WHERE p1.arttitle LIKE '%" + search + "%'";
        }
        String Sql = "SELECT p1.*,DATE_FORMAT(p1.putime,'%Y-%m-%d') AS addtime,p3.cat_name FROM article AS p1 " +
                    "LEFT JOIN article_info p2 ON p1.id = p2.article_id " +
                    "LEFT JOIN art_category p3 ON p1.art_cid = p3.id " +
                    where +
                    "ORDER BY p1.is_top DESC,p1.sort_no DESC, p1.id DESC  " +
                    "LIMIT " + startNum +", "+ limit;
        System.out.println(Sql);
        return Sql;
    }//*/

    public String addClickNum(@NotNull Map<String, Object> param)
    {
        String sql = "UPDATE article SET click_num=click_num+1 WHERE id = "+param.get("param1");
        return sql;
    }


}
